SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

/******************************************************************************/
/* Step 1. Remove stored procedure if it already exists                       */
/******************************************************************************/
IF (SELECT OBJECT_ID('[GrantManagement].[GrantsForServiceMember]')) IS NOT NULL
BEGIN
  DROP PROCEDURE [GrantManagement].[GrantsForServiceMember]
END

GO

/*****************************************************************************/
/* Stored Procedure :: GrantsFor                                             */
/*****************************************************************************/
CREATE PROCEDURE [GrantManagement].[GrantsForServiceMember]( @ServiceMemberID BIGINT )
AS  
BEGIN

	SELECT  Grants.OID as 'GrantsOID', 
			Grants.Version as 'GrantsVersion', 
			Grants.CaseID, 
			Grants.ApplicantID, 
			Grants.ValueOfGrant, 
			Grants.CheckNumber, 
			Grants.NameOnCreditCard, 
			Grants.GrantTypeID, 
			Grants.StateID, 
			Grants.PaymentTypeID, 
			Grants.DeliveryMethodID, 
			Grants.DeliverToPersonID, 
			Grants.DeliverToAddressID, 
			Grants.Approver1, 
			Grants.ApprovalDate1, 
			Grants.Approver2, 
			Grants.ApprovalDate2, 
			Grants.DisbursedBy, 
			Grants.DispersalDate, 
			Grants.VerifiedBy, 
			Grants.VerificationDate,
			Grants.UpdatedDate as 'GrantsUpdatedDate',
			Grants.CreatedDate as 'GrantsCreatedDate',

			Cases.OID as 'CaseOID', 
			Cases.Version as 'CaseVersion', 
			Cases.ServiceManID, 
			Cases.CaseVolunteerID, 
			Cases.CaseDirectorID,
			Cases.InjuryDate,

			ServiceMen.OID as 'ServiceMenOID', 
			ServiceMen.Version as 'ServiceMenVersion', 
			ServiceMen.PrimaryAddressID as 'ServiceMenPrimaryAddressID', 
			ServiceMen.PermanentAddressID as 'ServiceMenPermanentAddressID', 

			Applicants.OID as 'ApplicantsOID', 
			Applicants.Version as 'ApplicantsVersion', 
			Applicants.KinshipID, 
			Applicants.AddressID as 'ApplicantsAddressID', 

			People.OID as 'PeopleOID', 
			People.Version as 'PeopleVersion', 
			People.FirstName, 
			People.LastName, 
			People.MiddleInitial, 
			People.DOB, 
			People.Sex, 
			People.Phone1, 
			People.Phone2, 
			People.eMailAddress, 

			Users.OID as 'UsersOID', 
			Users.GUID, 
			Users.FullName, 
			Users.UPN, 
			Users.LastSignedIn

	From Grants 
	INNER JOIN Cases On Grants.CaseID = Cases.OID
	INNER JOIN ServiceMen On ServiceMen.OID = Cases.ServiceManID
	INNER JOIN Applicants On Grants.ApplicantID = Applicants.OID
	INNER JOIN People On Applicants.OID = People.OID
	INNER JOIN Users On Users.OID = Cases.CaseVolunteerID
	Where
		Cases.ServiceManID = @ServiceMemberID

END 
GO

/******************************************************************************/
/* Step 3. Assign appropriate permissions                                     */
/******************************************************************************/
GRANT EXECUTE ON [GrantManagement].[GrantsForServiceMember] TO [Liberty_Users]
GO
-- Developers and Admins have permissions to execute through membership   